import os
import numpy as np
import pandas as pd
from pyecharts import Bar
from pyecharts import Pie
import sqlite3
if __name__ =='__main':
    db_path = '../sqlite3'
else:
    db_path= 'db.sqlite3'

conn = sqlite3.connect(db_path,check_same_thread=False)

class Trade:
    def __init__(self):
        self.csv_to_db()
        self.df = pd.read_sql('SELECT * FROM DMSC_csv',conn)

    # 数据库
    def csv_to_db(self):
        file_name = 'chipotle.csv'
        conn = sqlite3.connect(r"E:\py_dj\pro\untitled21\app1\chipotle.csv")
        df = pd.read_csv(file_name, sep='\t')
        df.to_sql(file_name.replace('.', '_'), conn)

    def series_to_bar(self, series, name = None, table_name = None):
        bar = Bar(name)
        x = series.index
        y = np.round(series.values.ravel(), 1)
        bar.add(table_name, x, y, is_label_show=True, is_datazoom_show=True)
        return bar

    # 全部金额
    def all_money(self):
        all = (self.df.quantity * self.df.item_price).sum()
        return all

    # 销售最多的商品
    def most_commodity(self):
        c = self.df.groupby('item_name').count()
        c = c.sort_values(['quantity'], ascending=False)
        a = c.head(1).index[0]
        return a

    # 销售金额最多的商品
    def most_money_commodity(self):
        m = self.df.groupby('item_name').sum()
        m = m.sort_values(['alls'], ascending=False)
        m = m.head(1).index[0]
        return m

    # 每种商品的销售个数，做出表格
    def everyone_sales_num(self):
        s = self.df.groupby('item_name').sum()
        s.drop("alls", axis=1, inplace=True)
        s.drop("order_id", axis=1, inplace=True)
        s.drop("item_price", axis=1, inplace=True)
        bar = self.series_to_bar(s, '每种商品个数', '图表')
        return bar
    # 每种商品的销售金额，做出表格
    def everyone_money_commodity(self):
        m = self.df.groupby('item_name').sum()
        m.drop("quantity", axis=1, inplace=True)
        m.drop("order_id", axis=1, inplace=True)
        m.drop("item_price", axis=1, inplace=True)
        bar = self.series_to_bar(m, '每种商品的销售金额', '金额')
        return bar
    # 餐厅的产品的种类及对应的商品配料的价格，做出表格
    def everyone_money_commodity_dosing(self):
        m = self.df.groupby('item_name').sum()
        m.drop("quantity", axis=1, inplace=True)
        m.drop("order_id", axis=1, inplace=True)
        m.drop("item_price", axis=1, inplace=True)
        bar = self.series_to_bar(m,'餐厅的产品的种类及对应的商品配料的价格', '金额')
        return Bar
    #饼状图
    def series_to_pie(self, series, name=None, table_name=None):
        pie = Pie(name)
        x = series.index
        y = np.round(series.values.ravel(), 1)
        pie.add(table_name, x, y, is_label_show=True)
        return pie
    # 每种商品销售金额在流水总额中所占的比例，做圆饼图
    def everyone_money_to_all_money(self):
        m = self.df.groupby('item_name').sum()
        m.drop("quantity", axis=1, inplace=True)
        m.drop("order_id", axis=1, inplace=True)
        m.drop("item_price", axis=1, inplace=True)
        pie = self.series_to_pie(m, '每种商品销售金额在流水总额中所占的比例', '比例')
        return pie

    # 每个定单金额
    def everyone__order_form_money(self):
        m = self.df.groupby('order_id').sum()
        m.drop("quantity", axis=1, inplace=True)
        # m.drop("order_id", axis=1, inplace=True)
        m.drop("item_price", axis=1, inplace=True)
        bar = self.series_to_bar(m, '每个定单金额', '金额')
        return bar

    # 产品“Chicken Bowl”,选择最多的配料
    def chicken_bowl_most(self):
        a = self.df[self.df['item_name'] == 'Chicken Bowl']
        a = a.choice_description.value_counts().head(1).index[0]
        return a
    # 产品“Chicken Bowl”每种配料选择的次数，及所占比例，并做圆饼图
    def chicken_bowl(self):
        chicken = self.df[self.df['item_name'] == 'Chicken Bowl']
        a = chicken.choice_description.value_counts()
        x = a.index
        y = a
        pie = Pie('产品“Chicken Bowl”每种配料选择的次数，及所占比例')
        pie.add('比例', x, y, is_label_show=True)
        return pie

if __name__ == '__main__':

    x = Trade()

    info = x.all_money()

    print(info)